
import pymysql
from pymysql.cursors import DictCursor

class MySQLSSHClient:
    def __init__(
        self,
        mysql_host: str = "rm-gw8a4drz5t1dde7g3.mysql.germany.rds.aliyuncs.com" ,
        mysql_port: int = 3306,
        mysql_user: str = "root",
        mysql_password: str = "suKeepgo123@cys",
        mysql_db: str = "cfx_t",

        # mysql_host: str = "localhost" ,
        # mysql_port: int = 3306,
        # mysql_user: str = "root",
        # mysql_password: str = "1q2w3e4r5t@",
        # mysql_db: str = "cfx_t"
    ):
        self.mysql_host = mysql_host
        self.mysql_port = mysql_port
        self.mysql_user = mysql_user
        self.mysql_password = mysql_password
        self.mysql_db = mysql_db

        self.db_conn = None


    def connect(self) -> bool:
        try:

            # 连接MySQL
            self.db_conn = pymysql.connect(
                host=self.mysql_host,
                port=self.mysql_port,
                user=self.mysql_user,
                password=self.mysql_password,
                database=self.mysql_db,
                charset="utf8mb4",
                cursorclass=DictCursor
            )
            #print("MySQL连接成功")
            return True
        except Exception as e:
            print(f"连接失败: {str(e)}")
            self.close()  # 确保资源释放
            return False

    def query(self, sql: str):
        """
        执行查询SQL
        
        :param sql: SQL查询语句
        :return: 元组(查询结果数据, 列名列表)
        """
        if not self.db_conn:
            raise Exception("请先调用connect()建立连接")

        try:
            with self.db_conn.cursor() as cursor:
                cursor.execute(sql)
                # 获取查询结果
                data = cursor.fetchall()
                # 获取列名
                columns = [desc[0] for desc in cursor.description]
                return data, columns
        except pymysql.MySQLError as e:
            print(f"查询失败: {str(e)}")
            raise  # 抛出异常让调用者处理

    def close(self) -> None:
        """关闭所有连接"""
        # 关闭MySQL连接
        if self.db_conn and self.db_conn.open:
            self.db_conn.close()
            #print("MySQL连接已关闭")


    def __enter__(self):
        """支持上下文管理器"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器退出时关闭连接"""
        self.close()


# 使用示例
if __name__ == "__main__":
    

    # 方法1: 常规使用
    client = MySQLSSHClient()
    if client.connect():
        try:
            data, columns = client.query("SELECT * FROM user_channel_item LIMIT 10;")
            print("列名:", columns)
            print("数据:")
            for row in data:
                print(row)
        finally:
            client.close()


